package com.yonyou.brigade.util.operateutil;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

    /**
     * 根据fileType不同读取excel文件
     * @param path
     * @param path
     * @throws IOException
     */
    public static List<List<String>> readExcel(String path) {
        String fileType = path.substring(path.lastIndexOf(".") + 1);
        // return a list contains many list
        List<List<String>> lists = new ArrayList<List<String>>();
        //读取excel文件
        InputStream is = null;
        try {
            is = new FileInputStream(path);
            //获取工作薄
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(is);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(is);
            } else {
                return null;
            }

            //读取第一个工作页sheet
            Sheet sheet = wb.getSheetAt(0);
            //第一行为标题
            for (Row row : sheet) {
                ArrayList<String> list = new ArrayList<String>();
                for (Cell cell : row) {
                    //根据不同类型转化成字符串
                    cell.setCellType(CellType.STRING);
                    list.add(cell.getStringCellValue());
                }
                lists.add(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (is != null) is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }


    public static  void doExportExcelBySomeFiled(String sheetName, String titleName, String[] headers, Collection<?> dataSet, String resultUrl, HttpServletResponse response, String pattern, String []exportfileds) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个工作表
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置工作表默认列宽度为20个字节
        sheet.setDefaultColumnWidth((short) 20);
        //在工作表中合并首行并居中
        if (headers != null && headers.length > 0) {
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,headers.length-1));
        }
        // 创建[标题]样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        // 设置[标题]样式
        titleStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_BLUE.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        //创建[标题]字体
        HSSFFont titleFont = workbook.createFont();
        //设置[标题]字体
        titleFont.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        titleFont.setFontHeightInPoints((short) 24);
//        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        titleFont.setBold(true);
        // 把[标题字体]应用到[标题样式]
        titleStyle.setFont(titleFont);
        // 创建[列首]样式
        HSSFCellStyle headersStyle = workbook.createCellStyle();
        // 设置[列首]样式
        headersStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_ORANGE.getIndex());
        headersStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headersStyle.setBorderBottom(BorderStyle.THIN);
        headersStyle.setBorderLeft(BorderStyle.THIN);
        headersStyle.setBorderRight(BorderStyle.THIN);
        headersStyle.setBorderTop(BorderStyle.THIN);
        headersStyle.setAlignment(HorizontalAlignment.CENTER);
        //创建[列首]字体
        HSSFFont headersFont = workbook.createFont();
        //设置[列首]字体
        headersFont.setColor(HSSFColor.HSSFColorPredefined.VIOLET.getIndex());
        headersFont.setFontHeightInPoints((short) 12);
        headersFont.setBold(true);
        // 把[列首字体]应用到[列首样式]
        headersStyle.setFont(headersFont);
        // 创建[表中数据]样式
        HSSFCellStyle dataSetStyle = workbook.createCellStyle();
        // 设置[表中数据]样式
        dataSetStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GOLD.getIndex());
        dataSetStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        dataSetStyle.setBorderBottom(BorderStyle.THIN);
        dataSetStyle.setBorderLeft(BorderStyle.THIN);
        dataSetStyle.setBorderRight(BorderStyle.THIN);
        dataSetStyle.setBorderTop(BorderStyle.THIN);
        dataSetStyle.setAlignment(HorizontalAlignment.CENTER);
        dataSetStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 创建[表中数据]字体
        HSSFFont dataSetFont = workbook.createFont();
        // 设置[表中数据]字体
//        dataSetFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        dataSetFont.setBold(false);
        dataSetFont.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        // 把[表中数据字体]应用到[表中数据样式]
        dataSetStyle.setFont(dataSetFont);
        //创建标题行-增加样式-赋值
        HSSFRow titleRow = sheet.createRow(0);
        HSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellStyle(titleStyle);
        titleCell.setCellValue(titleName);
        // 创建列首-增加样式-赋值
        HSSFRow row = sheet.createRow(1);
        for (short i = 0; i < headers.length; i++) {
            @SuppressWarnings("deprecation")
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(headersStyle);
            if ("机票订单".equals(titleName)) {
                dealWithFlightData(headers[i], cell);
            } else if ("酒店订单".equals(titleName)) {
                dealWithHotelData(headers[i], cell);
            } else if ("火车票订单".equals(titleName)) {
                dealWithTrainData(headers[i], cell);
            } else if ("用车订单".equals(titleName)) {
                dealWithCarData(headers[i], cell);
            }
        }
        // 创建表中数据行-增加样式-赋值
        Iterator<?> it = dataSet.iterator();
        int index = 1;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            Object t = it.next();
            // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
//            Field[] fields = t.getClass().getDeclaredFields();
            for (short i = 0; i < exportfileds.length; i++) {
                @SuppressWarnings("deprecation")
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(dataSetStyle);
//                Field field = exportfileds[i];
                String fieldName = exportfileds[i];
                if (!StringUtils.isEmpty(fieldName)) {
                    if ("酒店订单".equals(titleName)) {
                        if ("orderNo".equals(fieldName)) {
                            fieldName = "orderNO";
                        }
                    }
                    String getMethodName = "get"+ fieldName.substring(0, 1).toUpperCase()+ fieldName.substring(1);
                    try {
                        @SuppressWarnings("rawtypes")
                        Class tCls = t.getClass();
                        @SuppressWarnings("unchecked")
                        Method getMethod = tCls.getMethod(getMethodName,new Class[] {});
                        Object value = getMethod.invoke(t, new Object[] {});
                        // 如果是时间类型,按照格式转换
                        String textValue = null;
                        if (value instanceof Date) {
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            textValue = sdf.format(date);
                        } else {
                            // 其它数据类型都当作字符串简单处理
                            if (value != null) {
                                textValue = value.toString();
                            }
                        }
                        // 利用正则表达式判断textValue是否全部由数字组成
                        if (textValue != null) {
                            cell.setCellValue(textValue);
//                        Pattern p = Pattern.compile("^\\d+(\\.\\d+)?$");
//                        Matcher matcher = p.matcher(textValue);
//                        if (matcher.matches()) {
//                            // 是数字当作double处理
//                            cell.setCellValue(Double.parseDouble(textValue));
//                        } else {
//                            // 不是数字做普通处理
//
//                        }
                        }
                    } catch (SecurityException e) {
                        e.printStackTrace();
                    } catch (NoSuchMethodException e) {
                        e.printStackTrace();
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        e.printStackTrace();
                    } finally {
                    //清理资源
                    try {
                        workbook.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    }
                }
            }
        }
        OutputStream out=null;
        try {
            if(resultUrl !=null){
                out = new FileOutputStream(resultUrl);
            }else {
                response.setHeader("Content-Disposition", "attachment; filename=" + java.net.URLEncoder.encode(sheetName + ".xls", "UTF-8"));
                response.setHeader("Content-Type", "application/vnd.ms-excel");
                out = response.getOutputStream();
            }
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static void dealWithCarData(String header, HSSFCell cell) {
        if ("actualPrice".equals(header)) {
            cell.setCellValue("实付金额");
        } else if ("approveStatus".equals(header)) {
            cell.setCellValue("审批状态");
        } else if ("arrearsPay".equals(header)) {
            cell.setCellValue("本单欠费金额");
        } else if ("arriveTime".equals(header)) {
            cell.setCellValue("到达时间");
        } else if ("bxState".equals(header)) {
            cell.setCellValue("报销状态");
        } else if ("callPhone".equals(header)) {
            cell.setCellValue("叫车人手机号");
        } else if ("category".equals(header)) {
            cell.setCellValue("商旅标记");
        } else if ("cityName".equals(header)) {
            cell.setCellValue("出发城市");
        } else if ("companyRealPay".equals(header)) {
            cell.setCellValue("公司实付金额");
        } else if ("companyRealRefund".equals(header)) {
            cell.setCellValue("公司实际退款金额");
        } else if ("costCenterName".equals(header)) {
            cell.setCellValue("成本中心");
        } else if ("couponPay".equals(header)) {
            cell.setCellValue("券抵扣金额");
        } else if ("createBy".equals(header)) {
            cell.setCellValue("预订人");
        } else if ("department".equals(header)) {
            cell.setCellValue("部门");
        } else if ("departureTime".equals(header)) {
            cell.setCellValue("出发时间");
        } else if ("distance".equals(header)) {
            cell.setCellValue("本单里程");
        } else if ("driverPhone".equals(header)) {
            cell.setCellValue("司机手机号");
        } else if ("duration".equals(header)) {
            cell.setCellValue("本单时长");
        } else if ("employeeNumber".equals(header)) {
            cell.setCellValue("员工编号");
        } else if ("endName".equals(header)) {
            cell.setCellValue("到达地点");
        } else if ("fromType".equals(header)) {
            cell.setCellValue("预定来源");
        } else if ("inApproveId".equals(header)) {
            cell.setCellValue("关联申请单号");
        } else if ("inCreateTime".equals(header)) {
            cell.setCellValue("预定时间");
        } else if ("isDomc".equals(header)) {
            cell.setCellValue("国内国际类型");
        } else if ("isInvoice".equals(header)) {
            cell.setCellValue("开票状态");
        } else if ("isNote".equals(header)) {
            cell.setCellValue("记事状态");
        } else if ("linkerName".equals(header)) {
            cell.setCellValue("联系人");
        } else if ("linkerPhone".equals(header)) {
            cell.setCellValue("联系人手机号");
        } else if ("orderNo".equals(header)) {
            cell.setCellValue("订单号");
        } else if ("orderStatus".equals(header)) {
            cell.setCellValue("订单状态");
        } else if ("otherInfo".equals(header)) {
            cell.setCellValue("备注");
        } else if ("outCompanyId".equals(header)) {
            cell.setCellValue("公司ID");
        } else if ("outUserCode".equals(header)) {
            cell.setCellValue("用户在第三方的编号");
        } else if ("payTime".equals(header)) {
            cell.setCellValue("支付时间");
        } else if ("payType".equals(header)) {
            cell.setCellValue("支付类型");
        } else if ("paymentType".equals(header)) {
            cell.setCellValue("支付方式");
        } else if ("personRealPay".equals(header)) {
            cell.setCellValue("个人实付金额");
        } else if ("personRealRefund".equals(header)) {
            cell.setCellValue("个人实际退款金额");
        } else if ("platCode".equals(header)) {
            cell.setCellValue("商家编码");
        } else if ("psgName".equals(header)) {
            cell.setCellValue("乘车人");
        } else if ("psgPhone".equals(header)) {
            cell.setCellValue("乘车人手机号");
        } else if ("publicOrPrivate".equals(header)) {
            cell.setCellValue("因公因私");
        } else if ("sourceType".equals(header)) {
            cell.setCellValue("来源");
        } else if ("startName".equals(header)) {
            cell.setCellValue("出发地点");
        } else if ("totalPrice".equals(header)) {
            cell.setCellValue("总费用");
        } else if ("type".equals(header)) {
            cell.setCellValue("订单类型");
        } else if ("useCarConfigId".equals(header)) {
            cell.setCellValue("用车规则");
        } else if ("useCarSrv".equals(header)) {
            cell.setCellValue("用车类型");
        } else if ("vehicleCard".equals(header)) {
            cell.setCellValue("车牌号");
        } else if ("vehicleModel".equals(header)) {
            cell.setCellValue("车型");
        } else if ("arriveCity".equals(header)) {
            cell.setCellValue("到达城市");
        } else if ("assumeOrg".equals(header)) {
            cell.setCellValue("费用承担单位");
        } else if ("assumeDept".equals(header)) {
            cell.setCellValue("费用承担部门");
        } else if ("costProject".equals(header)) {
            cell.setCellValue("费用项目");
        } else if ("applyUser".equals(header)) {
            cell.setCellValue("实际申请人");
        } else if ("companion".equals(header)) {
            cell.setCellValue("实际同行人");
        } else if ("relateOtherNo".equals(header)) {
            cell.setCellValue("商旅平台keyId");
        } else if ("voucherDateTime".equals(header)) {
            cell.setCellValue("凭证生成时间");
        } else if ("voucherTypeName".equals(header)) {
            cell.setCellValue("凭证类型名称");
        } else if ("voucherNo".equals(header)) {
            cell.setCellValue("凭证号");
        } else if ("finOrgName".equals(header)) {
            cell.setCellValue("报销单位名称");
        } else if ("orgName".equals(header)) {
            cell.setCellValue("报销费用归属单位");
        } else if ("deptName".equals(header)) {
            cell.setCellValue("报销费用归属部门");
        } else if ("airPlaneNo".equals(header)) {
            cell.setCellValue("接送航班号");
        }
    }

    private static void dealWithTrainData(String header, HSSFCell cell) {
        if ("fromType".equals(header)) {
            cell.setCellValue("预定来源");
        } else if ("orderType".equals(header)) {
            cell.setCellValue("订单类型");
        } else if ("parentOrderNo".equals(header)) {
            cell.setCellValue("原单号");
        } else if ("orderNo".equals(header)) {
            cell.setCellValue("订单号");
        } else if ("createBy".equals(header)) {
            cell.setCellValue("预订人");
        } else if ("oaUserCode".equals(header)) {
            cell.setCellValue("员工编号");
        } else if ("psgName".equals(header)) {
            cell.setCellValue("乘车人");
        } else if ("psgPosition".equals(header)) {
            cell.setCellValue("乘车人职级");
        } else if ("trainCode".equals(header)) {
            cell.setCellValue("车次");
        } else if ("seatType".equals(header)) {
            cell.setCellValue("席别");
        } else if ("orderTime".equals(header)) {
            cell.setCellValue("预定时间");
        } else if ("trainDate".equals(header)) {
            cell.setCellValue("出发日期");
        } else if ("fromTime".equals(header)) {
            cell.setCellValue("出发时间");
        } else if ("toDate".equals(header)) {
            cell.setCellValue("到达日期");
        } else if ("toTime".equals(header)) {
            cell.setCellValue("到达时间");
        } else if ("fromDateTime".equals(header)) {
            cell.setCellValue("发车时间");
        } else if ("toDateTime".equals(header)) {
            cell.setCellValue("到站时间");
        } else if ("fromStation".equals(header)) {
            cell.setCellValue("出发站");
        } else if ("toStation".equals(header)) {
            cell.setCellValue("到达站");
        } else if ("tktPrice".equals(header)) {
            cell.setCellValue("票面价");
        } else if ("counterFee".equals(header)) {
            cell.setCellValue("服务费");
        } else if ("sumInsMoney".equals(header)) {
            cell.setCellValue("保险费");
        } else if ("sendTicketFee".equals(header)) {
            cell.setCellValue("送票费");
        } else if ("carrierChangeFee".equals(header)) {
            cell.setCellValue("改签费");
        } else if ("tpPrice".equals(header)) {
            cell.setCellValue("退票费");
        } else if ("currency".equals(header)) {
            cell.setCellValue("结算币种");
        } else if ("paymentPrice".equals(header)) {
            cell.setCellValue("实付金额");
        } else if ("paymentType".equals(header)) {
            cell.setCellValue("支付方式");
        } else if ("preOrderMode".equals(header)) {
            cell.setCellValue("预订方式");
        } else if ("preiumReason".equals(header)) {
            cell.setCellValue("超标原因");
        } else if ("department1".equals(header)) {
            cell.setCellValue("部门1");
        } else if ("department2".equals(header)) {
            cell.setCellValue("部门2");
        } else if ("department3".equals(header)) {
            cell.setCellValue("部门3");
        } else if ("costCenter".equals(header)) {
            cell.setCellValue("成本中心1");
        } else if ("costCenter2".equals(header)) {
            cell.setCellValue("成本中心2");
        } else if ("costCenter3".equals(header)) {
            cell.setCellValue("成本中心3");
        } else if ("relateYBZID".equals(header)) {
            cell.setCellValue("关联申请单号");
        } else if ("relateOtherID".equals(header)) {
            cell.setCellValue("关联第三方唯一标识");
        } else if ("authStatus".equals(header)) {
            cell.setCellValue("授权状态");
        } else if ("bxStatus".equals(header)) {
            cell.setCellValue("报销状态");
        } else if ("note".equals(header)) {
            cell.setCellValue("备注");
        } else if ("defItem1".equals(header)) {
            cell.setCellValue("自定义字段1");
        } else if ("defItem2".equals(header)) {
            cell.setCellValue("自定义字段2");
        } else if ("defItem3".equals(header)) {
            cell.setCellValue("自定义字段3");
        } else if ("defItem4".equals(header)) {
            cell.setCellValue("自定义字段4");
        } else if ("defItem5".equals(header)) {
            cell.setCellValue("自定义字段5");
        } else if ("cardHolderName".equals(header)) {
            cell.setCellValue("持卡人");
        } else if ("orderStatus".equals(header)) {
            cell.setCellValue("订单状态");
        } else if ("ticketsNum".equals(header)) {
            cell.setCellValue("张数");
        } else if ("ticketsType".equals(header)) {
            cell.setCellValue("车票类型");
        } else if ("paperTicketFee".equals(header)) {
            cell.setCellValue("纸质票出票费");
        } else if ("payType".equals(header)) {
            cell.setCellValue("支付类型");
        } else if ("assumeOrg".equals(header)) {
            cell.setCellValue("费用申请单位");
        } else if ("costProject".equals(header)) {
            cell.setCellValue("费用项目");
        } else if ("applyUser".equals(header)) {
            cell.setCellValue("实际申请人");
        } else if ("companion".equals(header)) {
            cell.setCellValue("实际同行人");
        } else if ("relateOtherNo".equals(header)) {
            cell.setCellValue("商旅平台keyId");
        } else if ("voucherDateTime".equals(header)) {
            cell.setCellValue("凭证生成时间");
        } else if ("voucherTypeName".equals(header)) {
            cell.setCellValue("凭证类型名称");
        } else if ("voucherNo".equals(header)) {
            cell.setCellValue("凭证号");
        } else if ("finOrgName".equals(header)) {
            cell.setCellValue("报销单位名称");
        } else if ("orgName".equals(header)) {
            cell.setCellValue("报销费用归属单位");
        } else if ("deptName".equals(header)) {
            cell.setCellValue("报销费用归属部门");
        } else if ("assumeDept".equals(header)) {
            cell.setCellValue("费用申请部门");
        }
    }

    private static void dealWithHotelData(String header, HSSFCell cell) {
        if ("arrivalDate".equals(header)) {
            cell.setCellValue("入住日期");
        } else if ("authStatus".equals(header)) {
            cell.setCellValue("授权状态");
        } else if ("bxStatus ".equals(header)) {
            cell.setCellValue("报销状态");
        } else if ("cardHolderName".equals(header)) {
            cell.setCellValue("持卡人");
        } else if ("costCenter".equals(header)) {
            cell.setCellValue("成本中心1");
        } else if ("costCenter2".equals(header)) {
            cell.setCellValue("成本中心2");
        } else if ("costCenter3".equals(header)) {
            cell.setCellValue("成本中心3");
        } else if ("createBy".equals(header)) {
            cell.setCellValue("预订人");
        } else if ("createTime".equals(header)) {
            cell.setCellValue("预定时间");
        } else if ("currency".equals(header)) {
            cell.setCellValue("结算币种");
        } else if ("defItem1".equals(header)) {
            cell.setCellValue("自定义字段1");
        } else if ("defItem2".equals(header)) {
            cell.setCellValue("自定义字段2");
        } else if ("defItem3".equals(header)) {
            cell.setCellValue("自定义字段3");
        } else if ("defItem4".equals(header)) {
            cell.setCellValue("自定义字段4");
        } else if ("defItem5".equals(header)) {
            cell.setCellValue("自定义字段5");
        } else if ("department1".equals(header)) {
            cell.setCellValue("部门1");
        } else if ("department2".equals(header)) {
            cell.setCellValue("部门2");
        } else if ("department3".equals(header)) {
            cell.setCellValue("部门3");
        } else if ("departureDate".equals(header)) {
            cell.setCellValue("离店日期");
        } else if ("fromType".equals(header)) {
            cell.setCellValue("预定来源");
        } else if ("hotelCity".equals(header)) {
            cell.setCellValue("入住城市");
        } else if ("hotelLevel".equals(header)) {
            cell.setCellValue("住宿标准");
        } else if ("hotelName".equals(header)) {
            cell.setCellValue("酒店名称");
        } else if ("hotelStars".equals(header)) {
            cell.setCellValue("酒店星级");
        } else if ("hotelType".equals(header)) {
            cell.setCellValue("酒店类型");
        } else if ("inDays".equals(header)) {
            cell.setCellValue("入住天数");
        } else if ("inNights".equals(header)) {
            cell.setCellValue("间夜");
        } else if ("isDomc".equals(header)) {
            cell.setCellValue("国内国际类型");
        } else if ("oauserCode".equals(header)) {
            cell.setCellValue("员工编号");
        } else if ("orderNo".equals(header)) {
            cell.setCellValue("订单号");
        } else if ("orderStatus".equals(header)) {
            cell.setCellValue("订单状态");
        } else if ("orgId".equals(header)) {
            cell.setCellValue("组织号");
        } else if ("payType".equals(header)) {
            cell.setCellValue("支付类型");
        } else if ("paymentPrice".equals(header)) {
            cell.setCellValue("实付金额");
        } else if ("paymentType".equals(header)) {
            cell.setCellValue("支付方式");
        } else if ("preOrderMode".equals(header)) {
            cell.setCellValue("预定方式");
        } else if ("preiumReason".equals(header)) {
            cell.setCellValue("超标原因");
        } else if ("psgName".equals(header)) {
            cell.setCellValue("入住人");
        } else if ("psgNums".equals(header)) {
            cell.setCellValue("入住人数");
        } else if ("relateOtherID".equals(header)) {
            cell.setCellValue("关联第三方唯一标识");
        } else if ("relateYBZID".equals(header)) {
            cell.setCellValue("关联申请单号");
        } else if ("roomName".equals(header)) {
            cell.setCellValue("房型");
        } else if ("roomNums".equals(header)) {
            cell.setCellValue("房间数");
        } else if ("serviceFee".equals(header)) {
            cell.setCellValue("服务费");
        } else if ("statusType".equals(header)) {
            cell.setCellValue("订单状态");
        } else if ("contactMobile".equals(header)) {
            cell.setCellValue("联系人电话");
        } else if ("contactName".equals(header)) {
            cell.setCellValue("联系人姓名");
        } else if ("note".equals(header)) {
            cell.setCellValue("备注");
        } else if ("assumeOrg".equals(header)) {
            cell.setCellValue("费用承担单位");
        } else if ("assumeDept".equals(header)) {
            cell.setCellValue("费用承担部门");
        } else if ("costProject".equals(header)) {
            cell.setCellValue("费用项目");
        } else if ("applyUser".equals(header)) {
            cell.setCellValue("实际申请人");
        } else if ("companion".equals(header)) {
            cell.setCellValue("实际同行人");
        } else if ("relateOtherNo".equals(header)) {
            cell.setCellValue("商旅平台keyId");
        } else if ("voucherDateTime".equals(header)) {
            cell.setCellValue("凭证生成时间");
        } else if ("voucherTypeName".equals(header)) {
            cell.setCellValue("凭证类型名称");
        } else if ("voucherNo".equals(header)) {
            cell.setCellValue("凭证号");
        } else if ("finOrgName".equals(header)) {
            cell.setCellValue("报销单位名称");
        } else if ("orgName".equals(header)) {
            cell.setCellValue("报销费用归属单位");
        } else if ("deptName".equals(header)) {
            cell.setCellValue("报销费用归属部门");
        }
    }

    private static void dealWithFlightData(String header, HSSFCell cell) {
        if ("orderNo".equals(header)) {
            cell.setCellValue("订单号");
        } else if ("fromType".equals(header)) {
            cell.setCellValue("预定来源");
        } else if ("orderType".equals(header)) {
            cell.setCellValue("订单类型");
        } else if ("oldOrderNo".equals(header)) {
            cell.setCellValue("原单号");
        } else if ("orderStatus".equals(header)) {
            cell.setCellValue("订单状态");
        } else if ("publicOrPrivate".equals(header)) {
            cell.setCellValue("因公因私");
        } else if ("createBy".equals(header)) {
            cell.setCellValue("预订人");
        } else if ("oaUserCode".equals(header)) {
            cell.setCellValue("员工编号");
        } else if ("psgName".equals(header)) {
            cell.setCellValue("乘机人");
        } else if ("psgLevel".equals(header)) {
            cell.setCellValue("乘机人职级");
        } else if ("cardNO".equals(header)) {
            cell.setCellValue("乘机人证件号");
        } else if ("boardPointName".equals(header)) {
            cell.setCellValue("出发城市名称");
        } else if ("boardPointAirPortName".equals(header)) {
            cell.setCellValue("出发城市机场名称");
        } else if ("offPointName".equals(header)) {
            cell.setCellValue("到达城市名称");
        } else if ("offPointAirPortName".equals(header)) {
            cell.setCellValue("到达城市机场名称");
        } else if ("isDomc".equals(header)) {
            cell.setCellValue("航班类型");
        } else if ("flightWay".equals(header)) {
            cell.setCellValue("航程");
        } else if ("carrierName".equals(header)) {
            cell.setCellValue("航空公司");
        } else if ("flightNO".equals(header)) {
            cell.setCellValue("航班号");
        } else if ("classCode".equals(header)) {
            cell.setCellValue("舱位编码");
        } else if ("classLevelName".equals(header)) {
            cell.setCellValue("舱位级别");
        } else if ("lowestPrice".equals(header)) {
            cell.setCellValue("最低价");
        } else if ("yPrice".equals(header)) {
            cell.setCellValue("全价");
        } else if ("classRebate".equals(header)) {
            cell.setCellValue("折扣");
        } else if ("farePrice".equals(header)) {
            cell.setCellValue("票面价");
        } else if ("consturctAndFuel".equals(header)) {
            cell.setCellValue("基建+燃油");
        } else if ("sumInsMoney".equals(header)) {
            cell.setCellValue("保险");
        } else if ("taxPrice".equals(header)) {
            cell.setCellValue("税金");
        } else if ("sendTicketFee".equals(header)) {
            cell.setCellValue("送票费");
        } else if ("counterFee".equals(header)) {
            cell.setCellValue("服务费");
        } else if ("sumCounterFee".equals(header)) {
            cell.setCellValue("订单总服务费");
        } else if ("bindCounterFee".equals(header)) {
            cell.setCellValue("绑定服务费");
        } else if ("changeFee".equals(header)) {
            cell.setCellValue("改签费");
        } else if ("tpPrice".equals(header)) {
            cell.setCellValue("退票费");
        } else if ("refundPrice".equals(header)) {
            cell.setCellValue("实退金额");
        } else if ("currency".equals(header)) {
            cell.setCellValue("结算币种");
        } else if ("paymentPrice".equals(header)) {
            cell.setCellValue("实付金额");
        } else if ("payType".equals(header)) {
            cell.setCellValue("支付类型");
        } else if ("paymentType".equals(header)) {
            cell.setCellValue("支付方式");
        } else if ("preOrderMode".equals(header)) {
            cell.setCellValue("预订方式");
        } else if ("psgType".equals(header)) {
            cell.setCellValue("乘客类型");
        } else if ("preDays".equals(header)) {
            cell.setCellValue("提前预定天数");
        } else if ("orderTime".equals(header)) {
            cell.setCellValue("预定时间");
        } else if ("flightDateTime".equals(header)) {
            cell.setCellValue("起飞时间");
        } else if ("flightEndDateTime".equals(header)) {
            cell.setCellValue("到达时间");
        } else if ("noPreOrderReason".equals(header)) {
            cell.setCellValue("未提前预定原因");
        } else if ("noOrderLowstReason".equals(header)) {
            cell.setCellValue("未预定最低价原因");
        } else if ("premiumReason".equals(header)) {
            cell.setCellValue("溢价预定原因");
        } else if ("department1".equals(header)) {
            cell.setCellValue("部门1");
        } else if ("department2".equals(header)) {
            cell.setCellValue("部门2");
        } else if ("department3".equals(header)) {
            cell.setCellValue("部门3");
        } else if ("costCenter1".equals(header)) {
            cell.setCellValue("成本中心1");
        } else if ("costCenter2".equals(header)) {
            cell.setCellValue("成本中心2");
        } else if ("costCenter3".equals(header)) {
            cell.setCellValue("成本中心3");
        } else if ("relateYBZID".equals(header)) {
            cell.setCellValue("关联申请单号");
        } else if ("relateOtherID".equals(header)) {
            cell.setCellValue("关联平台号");
        } else if ("authStatus".equals(header)) {
            cell.setCellValue("授权状态");
        } else if ("bxStatus".equals(header)) {
            cell.setCellValue("报销状态");
        } else if ("note".equals(header)) {
            cell.setCellValue("备注");
        } else if ("contactMobile".equals(header)) {
            cell.setCellValue("联系人电话");
        } else if ("contactName".equals(header)) {
            cell.setCellValue("联系人姓名");
        } else if ("ticketNO".equals(header)) {
            cell.setCellValue("票号");
        } else if ("assumeOrg".equals(header)) {
            cell.setCellValue("费用承担单位");
        } else if ("assumeDept".equals(header)) {
            cell.setCellValue("费用承担部门");
        } else if ("costProject".equals(header)) {
            cell.setCellValue("费用项目");
        } else if ("applyUser".equals(header)) {
            cell.setCellValue("实际申请人");
        } else if ("companion".equals(header)) {
            cell.setCellValue("实际出行人");
        } else if ("relateOtherNo".equals(header)) {
            cell.setCellValue("商旅平台keyId");
        } else if ("voucherDateTime".equals(header)) {
            cell.setCellValue("凭证生成时间");
        } else if ("voucherTypeName".equals(header)) {
            cell.setCellValue("凭证类型名称");
        } else if ("voucherNo".equals(header)) {
            cell.setCellValue("凭证号");
        } else if ("finOrgName".equals(header)) {
            cell.setCellValue("报销单位名称");
        } else if ("orgName".equals(header)) {
            cell.setCellValue("报销费用归属单位");
        } else if ("deptName".equals(header)) {
            cell.setCellValue("报销费用归属部门");
        } else if ("defItem1".equals(header)) {
            cell.setCellValue("自定义字段1");
        } else if ("defItem2".equals(header)) {
            cell.setCellValue("自定义字段2");
        } else if ("defItem3".equals(header)) {
            cell.setCellValue("自定义字段3");
        } else if ("defItem4".equals(header)) {
            cell.setCellValue("自定义字段4");
        } else if ("defItem5".equals(header)) {
            cell.setCellValue("自定义字段5");
        } else if ("sourceType".equals(header)) {
            cell.setCellValue("实际供应商");
        } else if ("timeSpan".equals(header)) {
            cell.setCellValue("飞行时长");
        }
    }

}
